﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ACLibs.DBHelper.MSSQL
{
    public class MSSQLAPI
    {
        #region 数据库初始化
        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        /// <param name="ConnectionString">连接字符串</param>
        /// <returns>是否初始化成功</returns>
        public static bool Init(string ConnectionString)
        {
            //检测
            if (MSSQLGConf.Init) { return true; }
            MSSQLGConf.Init = false;
            //设置连接字符串到公共变量
            MSSQLGConf.ConnString.ConnectionString = ConnectionString;

            //初始化连接对象
            if (MSSQLGConf.GConn == null)
            {
                MSSQLGConf.GConn = new SqlConnection(MSSQLGConf.ConnString.ConnectionString);
            }
            else
            {
                MSSQLGConf.GConn.ConnectionString = MSSQLGConf.ConnString.ConnectionString;
            }

            //订阅连接对象事件
            MSSQLGConf.GConn.StateChange += GSQLConnection_StateChange; //连接对象状态更改处理
            MSSQLGConf.GConn.InfoMessage += GSQLConnection_InfoMessage; //连接对象信息提示
            MSSQLGConf.GConn.Open();
            MSSQLGConf.GConn.Close();

            //设置公共变量
            MSSQLGConf.Init = true;
            return true;
        }

        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        /// <param name="Server">服务器地址</param>
        /// <param name="DBName">数据库名称</param>
        /// <param name="Username">用户名</param>
        /// <param name="Password">密码</param>
        /// <returns>是否初始化成功</returns>
        public static bool Init(string Server, string DBName, string Username, string Password)
        {
            //检测
            if (MSSQLGConf.Init) { return true; }
            MSSQLGConf.Init = false;
            //设置连接字符串到公共变量
            MSSQLGConf.ConnString.DataSource = Server;
            MSSQLGConf.ConnString.InitialCatalog = DBName;
            MSSQLGConf.ConnString.UserID = Username;
            MSSQLGConf.ConnString.Password = Password;

            //初始化连接对象
            if (MSSQLGConf.GConn == null)
            {
                MSSQLGConf.GConn = new SqlConnection(MSSQLGConf.ConnString.ConnectionString);
            }
            else
            {
                MSSQLGConf.GConn.ConnectionString = MSSQLGConf.ConnString.ConnectionString;
            }

            //订阅连接对象事件
            MSSQLGConf.GConn.StateChange += GSQLConnection_StateChange; //连接对象状态更改处理
            MSSQLGConf.GConn.InfoMessage += GSQLConnection_InfoMessage; //连接对象信息提示
            MSSQLGConf.GConn.Open();
            MSSQLGConf.GConn.Close();

            //设置公共变量
            MSSQLGConf.Init = true;
            return true;
        }

        /// <summary>
        /// 连接对象状态更改处理
        /// </summary>
        private static void GSQLConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            MSSQLGConf.ConnMessage = e.Message;
        }

        /// <summary>
        /// 连接对象信息提示
        /// </summary>
        private static void GSQLConnection_StateChange(object sender, StateChangeEventArgs e)
        {
            MSSQLGConf.ConnState = e.CurrentState;
        }


        #endregion

        #region 数据库普通一些处理方法
        /// <summary>
        /// 断开数据库连接
        /// </summary>
        public static void Disconnection()
        {
            MSSQLGConf.GConn.Close();
            MSSQLGConf.Init = false;
        }

        /// <summary>
        /// 测试数据库是否可以连接
        /// </summary>
        /// <param name="Server">服务器</param>
        /// <param name="DBName">数据库名称</param>
        /// <param name="User">用户</param>
        /// <param name="Pass">密码</param>
        /// <param name="Msg">返回信息</param>
        /// <returns>是否可以连接</returns>
        public static bool SQLServerTest(string Server, string DBName, string User, string Pass, out string Msg)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(new SqlConnectionStringBuilder()
                {
                    InitialCatalog = DBName,
                    DataSource = Server,
                    UserID = User,
                    Password = Pass
                }.ConnectionString))
                {
                    connection.Open();
                    connection.Close();
                    Msg = "连接成功";
                    return true;
                }
            }
            catch (SqlException sx)
            {
                Msg = $"连接失败,错误:{sx.Message}";
                return false;
            }
        }

        #endregion

        #region 获取整表
        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="Query">SQL查询语句</param>
        /// <returns></returns>
        public static DataTable SelectDataTable(string Query)
        {
            if (!MSSQLGConf.Init) throw new Exception("数据库未初始化");
            DataTable DT = new DataTable();
            using (SqlDataAdapter sda = new SqlDataAdapter(Query, MSSQLGConf.GConn))
            {
                sda.Fill(DT);
            }
            return DT;
        }

        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="Query">SQL查询语句</param>
        /// <param name="dcs">DataTable列结构数组</param>
        /// <returns></returns>
        public static DataTable SelectDataTable(string Query, DataColumn[] dcs)
        {
            if (!MSSQLGConf.Init) throw new Exception("数据库未初始化");
            DataTable DT = new DataTable();
            DT.Columns.Clear();
            DT.Columns.AddRange(dcs);
            using (SqlDataAdapter sda = new SqlDataAdapter(Query, MSSQLGConf.GConn))
            {
                sda.Fill(DT);
            }
            return DT;
        }

        /// <summary>
        /// 获取SQLTable结构到DataTable
        /// </summary>
        /// <param name="TableName">SQL表名称</param>
        /// <returns>返回指定表结构</returns>
        public static DataTable SelectSQLTableColumns(string TableName)
        {
            //检测
            if (!MSSQLGConf.Init) throw new Exception("数据库未初始化");
            //变量
            DataTable DT = new DataTable(TableName);
            string Query = $"select top 0 * from {TableName}";
            using (SqlDataAdapter sda = new SqlDataAdapter(Query, MSSQLGConf.GConn))
            {
                sda.Fill(DT);
            }
            return DT;
        }
        #endregion

        #region 获取第一行第一列
        /// <summary>
        /// 获取执行代码返回结果的第一行的第一列
        /// </summary>
        /// <param name="Query">SQL语句</param>
        /// <returns></returns>
        public static object GetExeScalarData(string Query)
        {
            if (!MSSQLGConf.Init) throw new Exception("数据库未初始化");
            using (SqlCommand scm = new SqlCommand(Query, MSSQLGConf.GConn))
            {
                MSSQLGConf.GConn.Open();
                object obj= scm.ExecuteScalar();
                MSSQLGConf.GConn.Close();
                return obj;
            }
        }
        #endregion 

        #region 插入数据
        /// <summary>
        /// 上传数据表到数据库
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="strings">唯一性的列名称</param>
        /// <returns></returns>
        public static object InsertValue(DataTable dataTable, string[] strings)
        {
            //检测
            if (!MSSQLGConf.Init) throw new Exception("数据库未初始化");
            //变量
            string tmpTBName = $"#AC_{dataTable.TableName}";
            //插入数据
            MSSQLGConf.GConn.Open();
            using (SqlTransaction stran = MSSQLGConf.GConn.BeginTransaction())
            {
                using (SqlBulkCopy sqlBulk = new SqlBulkCopy(MSSQLGConf.GConn, SqlBulkCopyOptions.CheckConstraints, stran))
                {
                    using (SqlCommand sqlComm = new SqlCommand("", MSSQLGConf.GConn))
                    {
                        sqlComm.Transaction = stran;
                        try
                        {
                            //新建临时表
                            sqlComm.CommandText = $"select * into {tmpTBName} from {dataTable.TableName} where 1!=1";
                            sqlComm.ExecuteNonQuery();

                            //数据通过BulkCopy上传到临时表
                            sqlBulk.DestinationTableName = tmpTBName;
                            sqlBulk.NotifyAfter = 0;
                            sqlBulk.WriteToServer(dataTable);

                            //临时表转移到主表
                            sqlComm.CommandText = $"insert into {dataTable.TableName} select distinct {ConvertToColumns(dataTable)} from {tmpTBName} b where not exists (select 1 from {dataTable.TableName} a {ConvertToWhere(strings)})";
                            sqlComm.ExecuteNonQuery();

                            //删除临时表
                            sqlComm.CommandText = $"drop table {tmpTBName}";
                            sqlComm.ExecuteNonQuery();

                            //事务提交
                            stran.Commit();
                            MSSQLGConf.GConn.Close();
                        }
                        catch (SqlException x)
                        {
                            //事务回滚
                            stran.Rollback();
                            MSSQLGConf.GConn.Close();
                            throw x;
                        }
                    }
                }

            }
            return true;
        }

        /// <summary>
        /// 转换表列名到字符串并过滤主键
        /// </summary>
        /// <param name="dataTable">数据表</param>
        /// <param name="PriKeyColumnIndex">主键(标识)列</param>
        /// <returns></returns>
        private static string ConvertToColumns(DataTable dataTable, int PriKeyColumnIndex = -1)
        {
            //设置主键（标识列）
            if (PriKeyColumnIndex != -1)
            {
                dataTable.Columns[PriKeyColumnIndex].AutoIncrement = true;
            }
            //获取除标识列之外的所有列名
            StringBuilder Cols = new StringBuilder();
            foreach (DataColumn item in dataTable.Columns)
            {
                if (!item.AutoIncrement)
                {
                    Cols.Append(item.ColumnName).Append(",");
                }
            }
            Cols.Remove(Cols.Length - 1, 1);
            return Cols.ToString();
        }

        /// <summary>
        /// 转换指定列相同为查询条件为SQL语句
        /// </summary>
        /// <param name="strings">列名称数组</param>
        /// <returns></returns>
        private static string ConvertToWhere(string[] strings)
        {
            StringBuilder sb = new StringBuilder();
            if (strings.Length == 0) { return ""; }
            sb.Append(" where ");
            for (int i = 0; i < strings.Length; i++)
            {
                sb.Append("a.").Append(strings[i]).Append(" = ").Append("b.").Append(strings[i]).Append(" and ");
            }

            sb.Remove(sb.Length - 4, 4);
            return sb.ToString();
        }
        #endregion

        #region 更新数据
        /// <summary>
        /// 执行SQL语句,更新数据
        /// </summary>
        /// <param name="Querys" type="List<String>">要执行的插入更新数据语句的列表</param>
        public static object UpdateValues(List<string> Querys)
        {
            //检测
            if (!MSSQLGConf.Init) throw new Exception("数据库未初始化");
            //变量
            int RValue = 0, Index = 0;
            MSSQLGConf.GConn.Open();
            //更新入数据
            using (SqlTransaction stran = MSSQLGConf.GConn.BeginTransaction())
            {
                using (SqlCommand scm = MSSQLGConf.GConn.CreateCommand())
                {
                    scm.Transaction = stran;
                    try
                    {
                        foreach (string item in Querys)
                        {
                            scm.CommandText = item;
                            RValue += Convert.ToInt32(scm.ExecuteNonQuery());
                            Index++;
                        }
                        stran.Commit();
                        MSSQLGConf.GConn.Close();
                        return RValue;

                    }
                    catch (SqlException x)
                    {
                        stran.Rollback();
                        MSSQLGConf.GConn.Close();
                        throw x;
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句,更新数据
        /// </summary>
        /// <param name="Querys">更新数据语句</param>
        /// <returns></returns>
        public static int UpdateValue(string Querys)
        {
            //检测
            if (!MSSQLGConf.Init) throw new Exception("数据库未初始化");
            //变量
            int RValue = 0;
            //启用SQL连接
            MSSQLGConf.GConn.Open();
            //更新入数据
            using (SqlTransaction stran = MSSQLGConf.GConn.BeginTransaction())
            {
                using (SqlCommand scm = MSSQLGConf.GConn.CreateCommand())
                {
                    scm.Transaction = stran;
                    try
                    {
                        scm.CommandText = Querys;
                        RValue = Convert.ToInt32(scm.ExecuteNonQuery());
                        stran.Commit();
                        MSSQLGConf.GConn.Close();
                        return RValue;
                    }
                    catch (SqlException x)
                    {
                        stran.Rollback();
                        MSSQLGConf.GConn.Close();
                        throw x;
                    }
                }
            }
        }
        #endregion
    }
}
